
//数据库创建与连接
CREATE DATABASE IF NOT EXISTS HotelManagement;
USE HotelManagement;

//核心表结构设计
--1. 客房类型表 (room_types)

CREATE TABLE room_types (
    type_id INT PRIMARY KEY AUTO_INCREMENT,
    type_name VARCHAR(50) NOT NULL UNIQUE,
    base_price DECIMAL(10,2) NOT NULL CHECK (base_price > 0)
);

--2. 客房信息表 (rooms)

CREATE TABLE rooms (
    room_id INT PRIMARY KEY AUTO_INCREMENT,
    room_number VARCHAR(10) NOT NULL UNIQUE,
    type_id INT NOT NULL,
    floor INT NOT NULL,
    status ENUM('Available', 'Occupied', 'Maintenance') DEFAULT 'Available',
    FOREIGN KEY (type_id) 
        REFERENCES room_types(type_id)
        ON DELETE RESTRICT
);

--3. 客户信息表 (customers)
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    id_card VARCHAR(18) UNIQUE NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100)
);

--4. 入住记录表 (bookings)

CREATE TABLE bookings (
    booking_id INT PRIMARY KEY AUTO_INCREMENT,
    room_id INT NOT NULL,
    customer_id INT NOT NULL,
    check_in_time DATETIME NOT NULL,
    check_out_time DATETIME,
    total_cost DECIMAL(12,2) DEFAULT 0.00,
    FOREIGN KEY (room_id) 
        REFERENCES rooms(room_id)
        ON DELETE CASCADE,
    FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

功能实现
--1. 自动状态更新触发器
-- 入住时更新房间状态
DELIMITER //
CREATE TRIGGER trg_after_booking_insert
AFTER INSERT ON bookings
FOR EACH ROW
BEGIN
    UPDATE rooms
    SET status = 'Occupied'
    WHERE room_id = NEW.room_id;
END//
DELIMITER ;

-- 退房时更新房间状态
DELIMITER //
CREATE TRIGGER trg_after_booking_update
AFTER UPDATE ON bookings
FOR EACH ROW
BEGIN
    IF NEW.check_out_time IS NOT NULL THEN
        UPDATE rooms
        SET status = 'Available'
        WHERE room_id = NEW.room_id;
    END IF;
END//
DELIMITER ;

--2. 统计存储过程

DELIMITER //
CREATE PROCEDURE StatRoomUsage(
    IN start_date DATE,
    IN end_date DATE
)
BEGIN
    SELECT 
        rt.type_name,
        SUM(DATEDIFF(
            LEAST(b.check_out_time, CONCAT(end_date, ' 23:59:59')),
            GREATEST(b.check_in_time, CONCAT(start_date, ' 00:00:00'))
        )) AS total_days,
        SUM(b.total_cost) AS total_revenue
    FROM bookings b
    JOIN rooms r ON b.room_id = r.room_id
    JOIN room_types rt ON r.type_id = rt.type_id
    WHERE b.check_in_time < CONCAT(end_date, ' 23:59:59')
      AND (b.check_out_time IS NULL OR b.check_out_time > CONCAT(start_date, ' 00:00:00'))
    GROUP BY rt.type_name;
END//
DELIMITER ;

--3. 空房查询视图

CREATE VIEW AvailableRooms AS
SELECT 
    r.room_id,
    r.room_number,
    rt.type_name,
    rt.base_price
FROM rooms r
JOIN room_types rt ON r.type_id = rt.type_id
WHERE r.status = 'Available'
AND NOT EXISTS (
    SELECT 1
    FROM bookings b
    WHERE b.room_id = r.room_id
    AND (
        (b.check_in_time <= NOW() AND NOW() <= b.check_out_time)
        OR (b.check_out_time IS NULL AND b.check_in_time <= NOW())
    )
);

//完整约束体系
--外键约束：已通过表定义实现
--检查约束：
ALTER TABLE room_types
ADD CONSTRAINT chk_price_positive CHECK (base_price > 0);

ALTER TABLE rooms
ADD CONSTRAINT chk_floor_positive CHECK (floor > 0);
--唯一约束：已通过UNIQUE关键字实现
--非空约束：已通过NOT NULL定义
